Release 10.1A: OpenEdge Data Management:
SQL Reference

CREATE TABLE

Creates a table definition. A table definition consists of a set of named column definitions for data values that will be stored in rows of the table. SQL provides two forms of the CREATE TABLE statement.

The first syntax form explicitly specifies column definitions. The second syntax form, with the AS query_expression clause, implicitly defines the columns using the columns in a query expression.

Syntax

CREATE TABLE [ owner_name.]table_name ( { column_definition | table_constraint }, ...) [ AREA area_name ]     [ progress_table_attribute_keyword value ] ; CREATE TABLE [ owner_name.]table_name [ (column_name [ NOT NULL] , ... ) ] [ AREA area_name ] AS query_expression ;

owner_name

Specifies the owner of the table. If the name is different from the user name of the user executing the statement, then the user must have DBA privileges.

table_name

Names the table you are defining.

column_definition

Syntax

column_name data_type   [ COLLATE case_insensitive | case_sensitive ] [ DEFAULT { literal | NULL | SYSDATE | SYSTIME | SYSTIME STAMP } ] [ column_constraint [ column_constraint , ... ] ]   [ progress_column_attribute_keyword value   [progress_column_attribute_keyword value ] ...]

column_name data_type

Names a column and associates a data type with it. The column names specified must be different from other column names in the table definition. The data_type must be supported by OpenEdge.

When a table contains more than one column, a comma separator is required after each column_definition except for the final column_definition.

COLLATE

Indicates the column’s case sensitivity. Note the default is case_sensitive.

case_insensitive

Indicates the column will be case insensitive. The value for case_insensitive can be any name ending in I (for example: BASIC_I).

case_sensitive

Indicates the column will be case sensitive. The value for case_sensitive can be any name ending in S (for example: BASIC_S). Note that case_sensitive is the default value for COLLATE.

DEFAULT

Specifies an explicit default value for a column. The column takes on the value if an INSERT statement does not include a value for the column. If a column definition omits the DEFAULT clause, the default value is NULL.

The DEFAULT clause accepts the following arguments:

literal	An integer, numeric, or string constant.
NULL	A null value.
SYSDATE	The current date. Valid only for columns defined with DATE data types. SYSDATE is equivalent to the Progress default keyword TODAY.
SYSTIME	The current time. A TIME value.
SYSTIMESTAMP	The current date and time. A TIMESTAMP value.

column_constraint

Specifies a constraint that will be applied while inserting or updating a value in the associated column.

progress_column_attribute_keyword value

Progress 4GL column attribute keyword and value. See the "Syntax for 4GL Attributes" section for a list of column attribute keywords.

table_constraint

Specifies a constraint that will be applied while inserting or updating a row in the table.

AREA area_name

Specifies the name of the storage area where data stored in the table is to be stored.

If the specified area does not exist, the database returns an error. If you do not specify an area, the default area is used.

progress_table_attribute_keyword value

Progress 4GL table attribute keyword and value. See the "Syntax for 4GL Attributes" section for a list of table attribute keywords.

AS query_expression

Specifies a query expression to use for the data types and data values of the table’s columns. The types and lengths of the columns of the query expression result become the types and lengths of the respective columns in the table created. The rows in the resultant set of the query expression are inserted into the table after creating the table. In this form of the CREATE TABLE statement, column names are optional. If omitted, the names of the table’s columns are taken from the column names of the query expression.

Examples

In the following CREATE TABLE supplier_item example, the user issuing the CREATE TABLE statement must have REFERENCES privilege on the itemno column of the table john.item:

CREATE TABLE supplier_item ( supp_no INTEGER NOT NULL PRIMARY KEY, item_no INTEGER NOT NULL REFERENCES john.item (itemno), qty INTEGER ) ;

The table will be created in the current owner schema.

The following CREATE TABLE statement explicitly specifies a table owner, gus:

CREATE TABLE account ( account integer, balance numeric (12,2), info char (84) ) ;

The following example shows the AS query_expression form of CREATE TABLE to create and load a table with a subset of the data in the customer table:

CREATE TABLE dealer (name, street, city, state) AS SELECT name, street, city, state FROM customer WHERE state IN ('CA','NY', 'TX') ;

The following example includes a NOT NULL column constraint and DEFAULT clauses for column definitions:

CREATE TABLE emp ( empno integer NOT NULL, deptno integer DEFAULT 10, join_date date DEFAULT NULL ) ;

The following example shows how to create a table with two columns, both of which have 4GL descriptions and column labels specified:

CREATE TABLE emp (      empno INTEGER NOT NULL UNIQUE        PRO_DESCRIPTION ’A unique number for each employee’        PRO_COL_LABEL ’Employee No.’      deptno INTEGER DEFAULT 21 NOT NULL        PRO_DESCRIPTION ’The department number of the employee’        PRO_COL_LABEL ’Dept. No.’ ) PRO_HIDDEN ’Y’ PRO_DESCRIPTION ’All Employees’;

The table itself has a description specified, and will be created as hidden.

Authorization

Must have DBA privilege, RESOURCE privilege or SELECT privilege.

Related statements

DROP TABLE